package com.lxy.demo.dao;

import com.lxy.demo.entity.Condition;
import com.lxy.demo.entity.ToursInfo;
import com.lxy.demo.utils.DruidManger;
import com.lxy.demo.utils.PageBean;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ToursInfoDaoImpl implements ToursInfoDao {
    QueryRunner qr = new QueryRunner(DruidManger.getDataSource());

    @Override
    public int addInfo(ToursInfo t) {
        String sql = " insert into toursInfo values(null,?,?,?,?) ";
        Object[] prams = {t.getIntroduce(), t.getPubTime(), t.getPrice(), t.getCityId()};
        try {
            return qr.update(sql, prams);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<ToursInfo> findInfoBySome(Condition condition, Integer p) {
        String sql = " select t.id,t.introduce,t.pubTime,t.price,c.cityName from city c,toursinfo t where c.`cityId` = t.`cityId` and 1=1 ";
        List<Object> list = new ArrayList<>();
        if (condition.getCityID() != null && !condition.getCityID().isEmpty()) {
            sql += " and c.cityId = ? ";
            list.add(condition.getCityID());
        }
        if (condition.getMax() != null && !condition.getMax().isEmpty()) {
            sql += " and price < ? ";
            list.add(condition.getMax());
        }
        if (condition.getMin() != null && !condition.getMin().isEmpty()) {
            sql += " and price > ? ";
            list.add(condition.getMin());
        }
        if (p != null && !p.equals("")) {
            Integer pageSize = new PageBean().getPageSize();
            sql += " limit ?,? ";
            list.add((p - 1) * pageSize);
            list.add(pageSize);
        }
        try {
            return qr.query(sql, new BeanListHandler<ToursInfo>(ToursInfo.class), list.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int findCount(Condition c) {
        return findInfoBySome(c, null).size();
//        String sql = " select count(1) from city,toursInfo where city.`cityId`=toursInfo.`cityId` ";
//        try {
//            Long lon = qr.query(sql,new ScalarHandler<>());
//            return lon.intValue();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return 0;
    }

    @Override
    public int delInfo(int id) {
        String sql = " delete from toursinfo where id = ? ";
        try {
            return qr.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updInfo(int id,ToursInfo toursInfo) {
        String sql = " update `toursinfo` set introduce = ? ,pubTime = ? ,price = ? ,cityId = ? where id = ?  ";
        String introduce = toursInfo.getIntroduce();
        String pubTime = toursInfo.getPubTime();
        Double price = toursInfo.getPrice();
        Integer cityId = toursInfo.getCityId();
        try {
            return qr.update(sql,introduce,pubTime,price,cityId,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public ToursInfo findOne(int id) {
        String sql = "  select * from toursinfo t,city c where t.cityId = c.cityId and  t.id = ? ";
        try {
            return qr.query(sql,new BeanHandler<ToursInfo>(ToursInfo.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
